

CREATE PROC SP_MIGRATION_OF_PART_NUMBERS

@END_CUSTOMER_ID INT,
@CAVIUM_PART_NUMBER VARCHAR(50),
@END_CUSTOMER_PART_NUMBER VARCHAR(50),
@ERROR_CODE VARCHAR(200) OUTPUT

AS
BEGIN
	DECLARE @DB_PRODUCT_ID INT	
		
	IF ( @END_CUSTOMER_ID > 0 )
	BEGIN
	
		SELECT @DB_PRODUCT_ID = PRODUCT_ID FROM PRODUCT, PROCESS_STEP WHERE PRODUCT.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID AND PART_NUMBER= @CAVIUM_PART_NUMBER AND PS_TYPE_NAME = 'FINISHED GOODS'	
		-- Insertion of End User Part Number
		IF ( @DB_PRODUCT_ID IS NOT NULL )
			BEGIN
				IF ( ( SELECT COUNT(*) FROM CUSTOMER_PARTNUMBERS WHERE CUSTOMER_ID = @END_CUSTOMER_ID AND PRODUCT_ID = @DB_PRODUCT_ID ) = 0 )
				BEGIN
					INSERT INTO CUSTOMER_PARTNUMBERS ( CUSTOMER_ID, PRODUCT_ID, CUSTOMER_PARTNUMBER, ODM_ID ) VALUES( @END_CUSTOMER_ID, @DB_PRODUCT_ID, @END_CUSTOMER_PART_NUMBER, null )					
					SET @ERROR_CODE = 'End user part number '+@END_CUSTOMER_PART_NUMBER+' saved Successfully'
				END
				ELSE
				BEGIN
					SET @ERROR_CODE = 'End user part number '+@END_CUSTOMER_PART_NUMBER+' already exists in data base'
				END
				
			END
		ELSE
			BEGIN
				SET @ERROR_CODE = 'Cavium part number could not found in database'
			END
	END
	ELSE
		BEGIN
			SET @ERROR_CODE = 'Invalid customer id'	
		END

END